# LuckPay Web Manage System
#
# Copyright (c) 2016 Lucky Byte, Inc.
#
express = require 'express'
pgsql   = require 'pg'
moment  = require 'moment'
fs      = require 'fs'
mkdirp  = require 'mkdirp'
path    = require 'path'
router = express.Router()
module.exports = router


router.all '/', (req, res, next) ->
    res.locals.stat_title = '消费代付汇总'

    # 如果没有提供日期，则仅查询当日
    res.locals.start_date = req.body.start_date or \
        req.query.start_date or moment().format('YYYY-MM-DD')
    res.locals.end_date = req.body.end_date or \
        req.query.end_date or moment().format('YYYY-MM-DD')

    where_clause = """
        o.sys_time::date >= '#{res.locals.start_date}'
        and o.sys_time::date <= '#{res.locals.end_date}'
    """
    res.locals.total = 1

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询消费统计
    await client.query \
        "select count(o.*) as count,
                sum(o.p1_fee) as fee,
                case when t.chnl in (1,4) then
                    sum(cast(json_extract_path_text(i_pkt,'4') as decimal)) / 100
                end as amt
            from pay_p1_order as o
            left join pay_trlist as t on
                t.uuid = o.t20_uuid and t.uuid_no = 1
            where #{where_clause}
            group by t.chnl", defer err, result
    if err then done(client); return next(err)
    res.locals.t20_stats = result.rows[0] or {}

    # 尚未代付统计
    await client.query \
        "select count(o.*) as count,
            case when t.chnl in (1,4) then
                sum(cast(json_extract_path_text(i_pkt,'4') as decimal)) / 100
            end as amt
            from pay_p1_order as o
            left join pay_trlist as t on
                t.uuid = o.t20_uuid and t.uuid_no = 1
            where #{where_clause} and o.status = 1
            group by t.chnl", defer err, result
    if err then done(client); return next(err)
    res.locals.t40_status_1 = result.rows[0] or {}

    # 尚未确认统计
    await client.query \
        "select count(o.*) as count,
            case when t.chnl = 3 then
                sum(cast(json_extract_path_text(i_pkt,'txnAmt') as decimal)) / 100
            end as amt
            from pay_p1_order as o
            left join pay_trlist as t on
                t.uuid = o.t40_uuid and t.incoming = false and
                json_extract_path_text(t.trinfo, 'code')::text = 't40'
            where #{where_clause} and o.status = 2
            group by t.chnl", defer err, result
    if err then done(client); return next(err)
    res.locals.t40_status_2 = result.rows[0] or {}

    # 代付失败统计
    await client.query \
        "select count(o.*) as count,
            case when t.chnl = 3 then
                sum(cast(json_extract_path_text(i_pkt,'txnAmt') as decimal)) / 100
            end as amt
            from pay_p1_order as o
            left join pay_trlist as t on
                t.uuid = o.t40_uuid and t.incoming = false and
                json_extract_path_text(t.trinfo, 'code')::text = 't40'
            where #{where_clause} and o.status = 3
            group by t.chnl", defer err, result
    if err then done(client); return next(err)
    res.locals.t40_status_3 = result.rows[0] or {}

    # 代付成功统计
    await client.query \
        "select count(o.*) as count,
            case when t.chnl = 3 then
                sum(cast(json_extract_path_text(i_pkt,'txnAmt') as decimal)) / 100
            end as amt
            from pay_p1_order as o
            left join pay_trlist as t on
                t.uuid = o.t40_uuid and t.incoming = false and
                json_extract_path_text(t.trinfo, 'code')::text = 't40'
            where #{where_clause} and o.status = 4
            group by t.chnl", defer err, result
    if err then done(client); return next(err)
    res.locals.t40_status_4 = result.rows[0] or {}
    done()
    res.render 'trstat/p1_order_sum'
